/**
 * 
 */
package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.sun.jmx.snmp.Timestamp;

import exception.DataAccessException;

import service.UserQueryHelper;
import utils.DBUtils;
import model.Administrator;
import model.Door;
import model.Log;
import model.Message;
import model.User;

/**
 * @ClassName: UserDaoImpl
 * @Description:
 * @author Hongming Wu
 * @date 2013-12-6
 * 
 */
public class UserDaoImpl implements UserDao {

	// User
	private static final String GET_USER_BYID = "select * from user where user_id=?";
	private static final String GET_USER_BYNAME = "select * from user where user_name=?";
	private static final String UPDATE_USER = "update user set first_name=?,last_name=?,user_gender=?,user_tel=?,birth=?,user_email=? where user_id=?";
	// Message
	private static final String CREATE_MSG = "insert into message(msg_ID,sender_id,msg_date,msg_content,msg_type,msg_status) values(null,?,?,?,?,?)";
	// Door
	private static final String OPEN_DOOR = "update door set status=? where door_id=?";

	@Override
	public User getUserByID(String userID) {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		User user = null;

		try {
			pstmt = conn.prepareStatement(GET_USER_BYID);
			pstmt.setString(1, userID);
			rset = pstmt.executeQuery();

			if (rset.next()) {
				user = new User();
				user.setUser_ID(userID);
				user.setUser_Name(rset.getString("user_name"));
				user.setUser_Pwd(rset.getString("user_pwd"));
				user.setTitle(rset.getString("title"));
				user.setFirst_Name(rset.getString("first_name"));
				user.setLast_Name(rset.getString("last_name"));
				user.setUser_Gender(rset.getString("user_gender"));
				user.setBirth(rset.getString("birth"));
				user.setUser_Tel(rset.getString("user_tel"));
				user.setUser_Email(rset.getString("user_email"));
				user.setAccess_ID(rset.getInt("access_id"));
				user.setExpire_Date(rset.getString("expire_Date"));
				user.setStatus(rset.getInt("status"));
				System.out.println("Get user : " + userID);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}
		return user;
	}

	@Override
	public User getUserByName(String userName) {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		User user = null;

		try {
			pstmt = conn.prepareStatement(GET_USER_BYNAME);
			pstmt.setString(1, userName);
			rset = pstmt.executeQuery();

			if (rset.next()) {
				user = new User();
				user.setUser_ID(rset.getString("user_id"));
				user.setUser_Name(userName);
				user.setUser_Pwd(rset.getString("user_pwd"));
				user.setTitle(rset.getString("title"));
				user.setFirst_Name(rset.getString("first_name"));
				user.setLast_Name(rset.getString("last_name"));
				user.setUser_Gender(rset.getString("user_gender"));
				user.setBirth(rset.getString("birth"));
				user.setUser_Tel(rset.getString("user_tel"));
				user.setUser_Email(rset.getString("user_email"));
				user.setAccess_ID(rset.getInt("access_id"));
				user.setExpire_Date(rset.getString("expire_Date"));
				user.setStatus(rset.getInt("status"));
				System.out.println("Get user : " + userName);
			}else{
				throw new DataAccessException("User " + userName
						+ " is not existed");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}
		return user;
	}

	@Override
	public void updateUser(User user) {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;

		try {
			pstmt = conn.prepareStatement(UPDATE_USER);
			pstmt.setString(1, user.getFirst_Name());
			pstmt.setString(2, user.getLast_Name());
			pstmt.setString(3, user.getUser_Gender());
			pstmt.setString(4, user.getUser_Tel());
			pstmt.setString(5, user.getBirth());
			pstmt.setString(6, user.getUser_Email());
			pstmt.setString(7, user.getUser_ID());
			pstmt.executeUpdate();
			System.out.println("Update user " + user.getUser_Name());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, null);
		}
	}

	private String generateSQL(UserQueryHelper helper, User user) {
		String baseSQL = "select * from log where 1=1 and operator='"
				+ user.getUser_ID() + "' ";
		if (helper != null) {
			if (StringUtils.isNotEmpty(helper.getDoor_ID())) {
				baseSQL += " and log_content like '%" + helper.getDoor_ID()
						+ "%'";
			}
		}
		baseSQL += " order by log_id desc";
		System.out.println("Generate SQL : " + baseSQL);
		return baseSQL;
	}

	@Override
	public Integer countMylogs(UserQueryHelper helper, User user) {
		String sql = this.generateSQL(helper, user);

		sql = sql.replace("*", "count(*) log_count");
		sql = sql.substring(0, sql.indexOf("order")).trim();

		System.out.println("countLogs: " + sql);

		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int logCount = 0;

		try {

			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();

			if (rset.next()) {
				logCount = rset.getInt("log_count");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}
		return logCount;
	}

	@Override
	public List<Log> getScopedLogs(UserQueryHelper helper, User user,
			int begin, int end) {
		String sql = this.generateSQL(helper, user);
		sql += " limit ?,?";
		System.out.println("get scoped logs:" + sql + "," + begin + "," + end);

		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<Log> logList = null;

		try {

			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, begin-1);
			pstmt.setInt(2, 3);
			rset = pstmt.executeQuery();
			logList = new ArrayList<Log>();

			while (rset.next()) {
				Log log = new Log();
				log.setLog_ID(rset.getInt("log_id"));
				log.setLog_Date(rset.getString("log_date"));
				log.setLog_Content(rset.getString("log_content"));
				log.setOperator(rset.getString("operator"));
				logList.add(log);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}
		return logList;
	}

	@Override
	public void createMsg(User user, Message msg) {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date a = new Date();
		String str = df.format(a);
		try {
			pstmt = conn.prepareStatement(CREATE_MSG);
			pstmt.setString(1, user.getUser_ID());
			pstmt.setString(2, str);
			pstmt.setString(3, msg.getMsg_Content());
			pstmt.setString(4, msg.getMsg_Type());
			pstmt.setString(5, "0");
			pstmt.executeUpdate();
			System.out.println("Create a new message !");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, null);
		}
	}

	@Override
	public void openDoor(Door door) {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;

		try {
			pstmt = conn.prepareStatement(OPEN_DOOR);
			pstmt.setInt(1, 1);
			pstmt.setString(2, door.getDoor_ID());
			pstmt.executeUpdate();
			System.out.println("Open door " + door.getDoor_ID());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, null);
		}

	}

}
